﻿using EnqueteMobile.Repository.Base.SqlTools;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Linq.Mapping;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace EnqueteMobile.Repository.Base
{
    public class Repository<T> : IRepository<T> where T : class
    {
        protected DbSet<T> entity;
        protected DbContext context;

        public Repository(DbContext context)
        {
            if (context == null) { throw new ArgumentNullException("entities"); }
            this.entity = context.Set<T>();
            this.context = context;
        }

        /// <summary> Recupera todos os objetos registrados no banco </summary>
        public IQueryable<T> GetAll()
        {
            IQueryable<T> result = this.entity.Cast<T>();
            return result;
        }

        /// <summary> Busca instância única do objeto pelo Id </summary>
        /// <param name="keyValues">type (table) keys</param>
        public T GetById(params object[] keyValues)
        {
            return this.entity.Find(keyValues) as T;
        }

        /// <summary>
        /// Add an item of the passed type to dabatase.
        /// </summary>
        /// <param name="entity">item to be added to table</param>
        public T Add(T entity)
        {
            return this.entity.Add(entity);
        }

        /// <summary>
        /// Add an collection of the passed type to database
        /// </summary>
        /// <param name="entityCollection"></param>
        /// <returns></returns>
        public ICollection<T> AddRange(ICollection<T> entityCollection)
        {
            return this.entity.AddRange(entityCollection).ToList();
        }

        /// <summary>
        /// Removes an item of the passed type from dabatase.
        /// </summary>
        /// <param name="entity">item to be removed from table</param>
        public void Remove(T entity)
        {
            this.entity.Remove(entity);
        }

        /// <summary>
        /// Updates data on database.
        /// </summary>
        /// <param name="entity">collection of items to be updated in the table</param>
        public void Update(T entity)
        {
            context.Entry(entity).State = System.Data.Entity.EntityState.Modified;
        }

        /// <summary>
        /// Inserts bulk data from the passed type to database.
        /// </summary>
        /// <param name="entity">collection of items to be added to the table</param>
        public void BulkInsert(ICollection<T> entityCollection)
        {
            SqlTransaction sqlTransaction = GetOpenSqlConnection().BeginTransaction();

            using (sqlTransaction)
            {
                try
                {
                    BulkInsert(entityCollection, sqlTransaction);

                    sqlTransaction.Commit();
                }
                catch (Exception ex)
                {
                    if (sqlTransaction != null)
                    {
                        sqlTransaction.Rollback();
                    }

                    throw ex;
                }
            }
        }

        /// <summary>
        /// Receives a SqlTransaction and uses its scope to insert bulk data from the passed type to database.
        /// </summary>
        /// <param name="entity">collection of items to be added to the table</param>
        public void BulkInsert(ICollection<T> entityCollection, SqlTransaction sqlTransaction)
        {
            using (BulkCopy bulkCopy = new BulkCopy(sqlTransaction))
            {
                using (DataTable dataTable = GetDataTable(entityCollection))
                {
                    bulkCopy.BulkInsert(dataTable, SqlBulkCopyOptions.CheckConstraints);
                }
            }
        }

        /// <summary>
        /// Updates bulk data from the passed type to database using bulk insert with a temporary table to update.
        /// </summary>
        /// <param name="entity">collection of items to be updated in the table</param>
        /// <param name="columnsToUpdate">array of PropertyInfo that indicates wich columns must be updated.</param>
        [Obsolete(@"Method BulkUpdate(ICollection<T> entityCollection, PropertyInfo[] columnsToUpdate) is deprecated. 
        Please use BulkUpdate(ICollection<T> entityCollection, params Expression<Func<T, object>>[] columnsToUpdate) instead.")]
        public void BulkUpdate(ICollection<T> entityCollection, PropertyInfo[] columnsToUpdate)
        {
            SqlTransaction sqlTransaction = GetOpenSqlConnection().BeginTransaction();

            using (sqlTransaction)
            {
                try
                {
                    string[] columnsToUpdateString = GetColumnNameCollection(columnsToUpdate);
                    BulkUpdate(entityCollection, sqlTransaction, columnsToUpdateString);

                    sqlTransaction.Commit();
                }
                catch (Exception ex)
                {
                    if (sqlTransaction != null)
                    {
                        sqlTransaction.Rollback();
                    }

                    throw ex;
                }
            }
        }

        /// <summary>
        /// Updates bulk data from the passed type to database using bulk insert with a temporary table to update.
        /// </summary>
        /// <param name="entity">collection of items to be updated in the table</param>
        /// <param name="columnsToUpdate">array of Expression<Func<T, object>> that indicates wich columns must be updated.</param>
        public void BulkUpdate(ICollection<T> entityCollection, params Expression<Func<T, object>>[] columnsToUpdate)
        {
            SqlTransaction sqlTransaction = GetOpenSqlConnection().BeginTransaction();

            using (sqlTransaction)
            {
                try
                {
                    string[] columnsToUpdateString = GetColumnNameCollection(columnsToUpdate);
                    BulkUpdate(entityCollection, sqlTransaction, columnsToUpdateString);

                    sqlTransaction.Commit();
                }
                catch (Exception ex)
                {
                    if (sqlTransaction != null)
                    {
                        sqlTransaction.Rollback();
                    }

                    throw ex;
                }
            }
        }

        /// <summary>
        /// Receives a SqlTransaction and uses its scope to update bulk data from the passed type to database using bulk insert with a temporary table to update.
        /// </summary>
        /// <param name="entity">collection of items to be updated in the table</param>
        /// <param name="columnsToUpdate">array of Expression<Func<T, object>> that indicates wich columns must be updated.</param>
        public void BulkUpdate(ICollection<T> entityCollection, SqlTransaction sqlTransaction, params Expression<Func<T, object>>[] columnsToUpdate)
        {
            string[] columnsToUpdateString = GetColumnNameCollection(columnsToUpdate);
            BulkUpdate(entityCollection, sqlTransaction, columnsToUpdateString);
        }

        /// <summary>
        /// Gets the repository context SqlConnection. If the connection is not open, it will open.
        /// </summary>
        public SqlConnection GetOpenSqlConnection()
        {
            SqlConnection sqlConnection = ((SqlConnection)context.Database.Connection);

            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }

            return sqlConnection;
        }

        private void BulkUpdate(ICollection<T> entityCollection, SqlTransaction sqlTransaction, params string[] columnsToUpdate)
        {
            using (BulkCopy bulkCopy = new BulkCopy(sqlTransaction))
            {
                using (DataTable dataTable = GetDataTable(entityCollection, true, columnsToUpdate))
                {
                    TempTable.Create(sqlTransaction, dataTable);

                    bulkCopy.BulkInsert(dataTable, SqlBulkCopyOptions.KeepIdentity);

                    UpdateFromTemporaryTable<T>(sqlTransaction, columnsToUpdate);

                    TempTable.Drop(sqlTransaction, dataTable.TableName);
                }
            }
        }

        private DataTable GetDataTable(ICollection<T> entityCollection, bool isTemporaryTable = false, params string[] columnsToCreate)
        {
            string tableName = (isTemporaryTable ? GetTemporaryTableName<T>() : GetTableName<T>());
            Dictionary<string, Type> columns = new Dictionary<string, Type>();
            ICollection<object[]> rows = new List<object[]>();

            ICollection<PropertyInfo> properties = typeof(T).GetProperties();

            if (columnsToCreate.Any())
            {
                properties = properties.Where(p => columnsToCreate.Contains(GetColumnName(p))).ToList();
                properties = properties.Concat(GetPrimaryKeyProperties<T>()).ToList();
            }

            foreach (PropertyInfo propertyInfo in properties)
            {
                columns.Add(GetColumnName(propertyInfo), GetColumnType(propertyInfo));
            }

            foreach (T entity in entityCollection)
            {
                rows.Add(properties.Select(property => property.GetValue(entity, null)).ToArray());
            }

            return DataTableHelper.Create(tableName, columns, rows);
        }

        private string GetTableName<T>()
        {
            System.ComponentModel.DataAnnotations.Schema.TableAttribute tableAttribute = typeof(T).GetCustomAttribute<System.ComponentModel.DataAnnotations.Schema.TableAttribute>();

            if (tableAttribute != null)
            {
                if (string.IsNullOrWhiteSpace(tableAttribute.Schema) == false)
                {
                    return "[" + tableAttribute.Schema + "]." + tableAttribute.Name;
                }
                return tableAttribute.Name;
            }

            return typeof(T).Name;
        }

        private string GetColumnName(PropertyInfo propertyInfo)
        {
            System.ComponentModel.DataAnnotations.Schema.ColumnAttribute columnAttribute = propertyInfo.GetCustomAttribute<System.ComponentModel.DataAnnotations.Schema.ColumnAttribute>();

            if (columnAttribute != null)
            {
                return columnAttribute.Name;
            }

            return propertyInfo.Name;
        }

        private string GetColumnName(Expression<Func<T, object>> columnExpression)
        {
            MemberExpression columnMemberExpression;

            if (columnExpression.Body is MemberExpression)
            {
                columnMemberExpression = (MemberExpression)columnExpression.Body;
            }
            else
            {
                columnMemberExpression = (MemberExpression)((UnaryExpression)columnExpression.Body).Operand;
            }

            System.ComponentModel.DataAnnotations.Schema.ColumnAttribute columnAttribute = columnMemberExpression.Member.GetCustomAttribute<System.ComponentModel.DataAnnotations.Schema.ColumnAttribute>();

            if (columnAttribute != null)
            {
                return columnAttribute.Name;
            }

            return columnMemberExpression.Member.Name;
        }

        private Type GetColumnType(PropertyInfo propertyInfo)
        {
            Type propertyType = propertyInfo.PropertyType;

            if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                propertyType = Nullable.GetUnderlyingType(propertyType);
            }

            return propertyType;
        }

        private string[] GetColumnNameCollection(PropertyInfo[] propertyInfoColumns)
        {
            List<string> columns = new List<string>();

            foreach (PropertyInfo propertyInfo in propertyInfoColumns)
            {
                columns.Add(GetColumnName(propertyInfo));
            }

            return columns.ToArray();
        }

        private string[] GetColumnNameCollection(Expression<Func<T, object>>[] expressionColumns)
        {
            List<string> columns = new List<string>();

            foreach (Expression<Func<T, object>> expressionCollumn in expressionColumns)
            {
                columns.Add(GetColumnName(expressionCollumn));
            }

            return columns.ToArray();
        }

        private void UpdateFromTemporaryTable<T>(SqlTransaction sqlTransaction, string[] columnsToUpdate)
        {
            string tableName = GetTableName<T>();
            string temporaryTableName = GetTemporaryTableName<T>();

            #region Update Columns
            List<string> updateColumnList = new List<string>();

            foreach (string columnToUpdate in columnsToUpdate)
            {
                updateColumnList.Add(String.Format("O.{0} = T.{0}", columnToUpdate));
            }

            string updateColumns = string.Join(",", updateColumnList);

            #endregion

            #region Update Join
            PropertyInfo[] primaryKeyProperties = GetPrimaryKeyProperties<T>();

            List<string> updateJoinList = new List<string>();

            foreach (PropertyInfo propertyInfo in primaryKeyProperties)
            {
                updateJoinList.Add(String.Format("O.{0} = T.{0}", GetColumnName(propertyInfo)));
            }

            string updateJoin = string.Join("AND", updateJoinList);
            #endregion

            string[] updateStringArguments = new string[] { updateColumns, tableName, temporaryTableName, updateJoin };

            string updateScript = String.Format(
                @"UPDATE O SET {0} 
                FROM {1} O (NOLOCK)
                INNER JOIN {2} T (NOLOCK) ON {3}", updateStringArguments);

            SqlCommand updateCommand = new SqlCommand(updateScript, sqlTransaction.Connection, sqlTransaction);
            updateCommand.ExecuteNonQuery();
        }

        private string GetTemporaryTableName<T>()
        {
            string tableName = GetTableName<T>();
            string temporaryTableName = new StringBuilder().Append("#").Append(tableName).ToString();

            return temporaryTableName;
        }

        private PropertyInfo[] GetPrimaryKeyProperties<T>()
        {
            PropertyInfo[] primaryKeyProperties = typeof(T).GetProperties().Where(p => p.CustomAttributes.Select(a => a.AttributeType).Contains(typeof(KeyAttribute))).ToArray();

            if (primaryKeyProperties.Count() == 0)
            {
                throw new KeyNotFoundException(String.Format("The class {0} has no column with KeyAttribute. The Key Attribute is used to indicate that the column is a primary key.", typeof(T).Name));
            }

            return primaryKeyProperties;
        }
    }
}